package com.boss.thirteen.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.sys.utils.SysUtils;
import com.boss.thirteen.dao.IThirteenAgencyVerifyDao;
import com.boss.thirteen.entity.ThirteenAgencyVerify;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
@Repository
public class ThirteenAgencyVerifyDaoImpl extends BaseDaoImpl implements IThirteenAgencyVerifyDao {
    @Override
    public List<ThirteenAgencyVerify> queryThirteenAgencyVerify(PaginationDTO paginationDTO, Map<String, Object> params,List<String> agencyIds) {
        List<Object> param=new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select id, biz_key, mould_key, create_time, update_time, agency_id, ")
                .append("salary_info_verified_code, is_submit_in_agency, is_salary_submit, ")
                .append("is_workflow_init, is_end, is_deleted, batch, is_pay_data_created,")
                .append(" is_bank_data_created, mof_div_code, salary_year year, is_verify_end, agency_code, agency_name ")
                .append(" from ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append(" where is_end=? and mof_div_code = ? and salary_year = ? and is_deleted=?");
        param.add(SystemDictConstant.IS_FALSE);
        param.add(params.get("mof_div_code"));
        param.add(params.get("year"));
        param.add(SystemDictConstant.NOT_DELETED);
        if(!SysUtils.isNullOrEmpty(params.get("mould_key"))){
            sql.append(" and mould_key = ? ");
            param.add(params.get("mould_key"));
        }
        if(!SysUtils.isNullOrEmpty(params.get("isSalarySubmit"))){
            sql.append(" and is_salary_submit = ? ");
            param.add(params.get("isSalarySubmit"));
        }
        if(!SysUtils.isNullOrEmpty(params.get("agency_id"))){
            sql.append(" and agency_id = ? ");
            param.add(params.get("agency_id"));
        }
        if(!CollectionUtils.isEmpty(agencyIds)){
            sql.append(" and  ").append(SysUtils.inSql("agency_id",agencyIds));
        }
        if(!SysUtils.isNullOrEmpty(params.get("biz_key"))){
            sql.append(" and biz_key = ? ");
            param.add(params.get("biz_key"));
        }
        sql.append(" order by agency_code ");
        String querySql = super.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] objects = super.boxUpParam(param.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql,new BeanPropertyRowMapper(ThirteenAgencyVerify.class), objects);
    }
    @Override
    public int queryThirteenAgencyCount( Map<String, Object> params,List<String> agencyIds) {
        List<Object> param=new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) ")
.append(" from ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append(" where is_end=? and mof_div_code = ? and salary_year = ? and is_deleted=? ");
        param.add(SystemDictConstant.IS_FALSE);
        param.add(params.get("mof_div_code"));
        param.add(params.get("year"));
        param.add(SystemDictConstant.NOT_DELETED);
        if(!SysUtils.isNullOrEmpty(params.get("mould_key"))){
            sql.append(" and mould_key = ? ");
            param.add(params.get("mould_key"));
        }
        if(!SysUtils.isNullOrEmpty(params.get("isSalarySubmit"))){
            sql.append(" and is_salary_submit = ? ");
            param.add(params.get("isSalarySubmit"));
        }
        if(!SysUtils.isNullOrEmpty(params.get("agency_id"))){
            sql.append(" and agency_id = ? ");
            param.add(params.get("agency_id"));
        }
        if(!CollectionUtils.isEmpty(agencyIds)){
            sql.append(" and  ").append(SysUtils.inSql("agency_id",agencyIds));
        }
        if(!SysUtils.isNullOrEmpty(params.get("biz_key"))){
            sql.append(" and biz_key = ? ");
            param.add(params.get("biz_key"));
        }
        sql.append(" order by agency_code ");
        return  this.getJdbcTemplate().queryForObject(sql.toString(),param.toArray(), Integer.class);
    }
    @Override
    public List<ThirteenAgencyVerify> selectThirteenAgencyVerify(Map<String, Object> params) {
        List<Object> param=new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select id, biz_key, mould_key, create_time, update_time, agency_id, ")
                .append("salary_info_verified_code, is_submit_in_agency, is_salary_submit, ")
                .append("is_workflow_init, is_end, is_deleted, batch, is_pay_data_created,")
                .append(" is_bank_data_created, mof_div_code, salary_year year, is_verify_end, agency_code, agency_name ")
                .append(" from ").append(TableName.THIRTEEN_AGENCY_VERIFY);
//        sql.append(" where  mof_div_code = ? and salary_year = ?  and is_deleted= ? ");
//        param.add(params.get("mof_div_code"));
//        param.add(params.get("year"));
//        param.add(SystemDictConstant.NOT_DELETED);
//        if(!SysUtils.isNullOrEmpty(params.get("mould_key"))){
//            sql.append(" and mould_key = ? ");
//            param.add(params.get("mould_key"));
//        }
//        if(!SysUtils.isNullOrEmpty(params.get("agency_id"))){
//            sql.append(" and agency_id = ? ");
//            param.add(params.get("agency_id"));
//        }
//        if(!SysUtils.isNullOrEmpty(params.get("biz_key"))){
//            sql.append(" and biz_key = ? ");
//            param.add(params.get("biz_key"));
//        }
        sql.append(" order by agency_code ");
        return this.getJdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper(ThirteenAgencyVerify.class),param.toArray());
    }

    @Override
    public void batchInsertThirteenAgencyVerify(List<ThirteenAgencyVerify> list) {
        StringBuilder sql = new StringBuilder();
        sql.append(" INSERT INTO  ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append("(id, biz_key, mould_key, create_time, update_time, agency_id, ")
        .append("salary_info_verified_code, is_submit_in_agency, is_salary_submit, ")
        .append("is_workflow_init, is_end, is_deleted, batch, is_pay_data_created,")
        .append(" is_bank_data_created, mof_div_code, salary_year, is_verify_end, agency_code, agency_name) ");
        sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        List<Object[]> params=new ArrayList<>();
        for(ThirteenAgencyVerify tav:list) {
            String uuid=SysUtils.generateGuid();
            params.add(new Object[]{
                    uuid,uuid, tav.getMouldKey(), String.valueOf(DateUtils.getCurrentTimeStamp()), String.valueOf(DateUtils.getCurrentTimeStamp()), tav.getAgencyId(),
                    SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE,
                    SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE, SystemDictConstant.NOT_DELETED, 1, SystemDictConstant.IS_FALSE,
                    SystemDictConstant.IS_FALSE, tav.getMofDivCode(), tav.getYear(), SystemDictConstant.IS_FALSE, tav.getAgencyCode(), tav.getAgencyName()
            });
        }
        this.getjdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void batchUpdateThirteenAgencyVerify(List<ThirteenAgencyVerify> list) {
        StringBuilder sql = new StringBuilder();
        sql.append("MERGE into ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append("t using (select ? s.id,? s.biz_key,? s.mould_key,? s.create_time,")
                .append("? s.update_time,? s.agency_id,? s.salary_info_verified_code,? s.is_submit_in_agency,")
                .append("? s.is_salary_submit,? s.is_workflow_init,? s.is_end,? s.is_deleted,? s.batch,? s.is_pay_data_created,")
                .append("? s.is_bank_data_created,? s.mof_div_code,? s.salary_year,? s.is_verify_end,? s.agency_code,? s.agency_name")
                .append("from sal_salary_base_relevance ) s on (t.id=s.id)")
                .append(" when matched then update set " )
                .append(" (t.id=t.id,t.biz_key=s.biz_key,? t.mould_key=s.mould_key,? t.create_time=s.create_time,")
                .append("t.update_time=s.update_time,t.agency_id=s.agency_id,? t.salary_info_verified_code=s.salary_info_verified_code, ")
                .append("t.is_submit_in_agency=s.is_submit_in_agency,")
                .append("t.is_salary_submit=s.is_salary_submit,t.is_workflow_init=s.is_workflow_init, t.is_end=s.is_end, t.is_deleted=s.is_deleted,")
                .append("t.batch=s.batch,t.is_pay_data_created=s.is_pay_data_created,")
                .append("t.is_bank_data_created=s.is_bank_data_created, t.mof_div_code=s.mof_div_code,t.salary_year=s.salary_year,t.is_verify_end=s.is_verify_end,")
                .append("t.agency_code=s.agency_code,t.agency_name=s.agency_name) ")
                .append(" when not matched then insert values " )
                .append(" ( s.id, s.biz_key,s.mould_key, s.create_time,")
        .append(" s.update_time, s.agency_id,s.salary_info_verified_code, is_submit_in_agency,")
        .append(" s.is_salary_submit, s.is_workflow_init, s.is_end, s.is_deleted, s.batch, s.is_pay_data_created,")
        .append(" s.is_bank_data_created, s.mof_div_code,s.salary_year, s.is_verify_end, s.agency_code, s.agency_name ) ");
        List<Object[]> params=new ArrayList<>();
        for(ThirteenAgencyVerify tav:list) {
            params.add(new Object[]{
                    tav.getId(),tav.getBizKey(), tav.getMouldKey(),tav.getCreateTime(), String.valueOf(DateUtils.getCurrentTimeStamp()), tav.getAgencyId(),
                    SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE,
                    tav.getIsWorkflowInit(), SystemDictConstant.IS_FALSE, tav.getIsDeleted(), 1, SystemDictConstant.IS_FALSE,
                    SystemDictConstant.IS_FALSE, tav.getMofDivCode(), tav.getYear(), SystemDictConstant.IS_FALSE, tav.getAgencyCode(), tav.getAgencyName()
            });
        }
        this.getjdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void batchUpdateThirteenAgencyStatus(List<ThirteenAgencyVerify> list) {
        StringBuilder sql = new StringBuilder();
        sql.append("update  ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append(" set is_salary_submit=? ,salary_info_verified_code=?")
                .append(" where biz_key=? ");
        List<Object[]> params=new ArrayList<>();
        for(ThirteenAgencyVerify tav:list) {
            params.add(new Object[]{
                    tav.getIsSalarySubmit(),tav.getAgencyInfoVerifiedCode(),tav.getBizKey()
            });
        }
        this.getjdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deleteThirteenAgencyVerify(Map<String, Object> params) {

    }

    @Override
    public ThirteenAgencyVerify getThirteenInfoByAgencyId(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" id, biz_key, mould_key, create_time, update_time, agency_id, salary_info_verified_code, ");
        sql.append(" is_submit_in_agency, is_salary_submit, is_workflow_init, is_end, is_deleted, audit_explain, ");
        sql.append(" batch, is_pay_data_created, is_bank_data_created, mof_div_code, salary_year year, is_verify_end, agency_code, agency_name, payment_create_time ");
        sql.append(" FROM ").append(TableName.THIRTEEN_AGENCY_VERIFY);
//        sql.append(" where is_end = ? ");
//        sql.append(" and agency_id = ?");
//        sql.append(" and is_deleted = ?");
        try {
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(ThirteenAgencyVerify.class),
                    new Object[]{SystemDictConstant.IS_FALSE,
                            agencyId,SystemDictConstant.NOT_DELETED});
        } catch (EmptyResultDataAccessException e) {
            return  new ThirteenAgencyVerify();
        }
    }

    @Override
    public Map<String, Object> selectStateByAgencyId(String agencyId, String... columnNames) {
        if(columnNames == null || columnNames.length == 0){
            return Collections.emptyMap();
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" select ");
        for (int i = 0; i < columnNames.length; i++) {
            sb.append(columnNames[i]);
            if (i != columnNames.length - 1) {
                sb.append(" , ");
            }
        }
        sb.append(" from ")
                .append(TableName.THIRTEEN_AGENCY_VERIFY)
                .append(" where ")
                .append(" is_deleted = 0 and agency_id = ? ")
                .append(" and is_end = 0 ");
//        如果不是叶子机构，会查不到数据。
        try{
            return super.getjdbcTemplate().queryForMap(sb.toString(), agencyId);
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyMap();
        }
    }

    @Override
    public List<ThirteenAgencyVerify> getAgencyVerifyByAgencyIds(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" id, biz_key,mould_key, create_time, update_time, agency_id, salary_year year,");
        sql.append(" is_submit_in_agency, is_salary_submit,");
        sql.append(" is_end, is_deleted, audit_explain,is_pay_data_created, is_bank_data_created,agency_code, agency_name ");
        sql.append(" FROM ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append(" where is_end = 0 and is_deleted=? ");
        Object[] objects=new Object[]{
                SystemDictConstant.NOT_DELETED
        };
        if (!SysUtils.isNullOrEmpty(agencyIds) && agencyIds.size() > 0) {
            sql.append(" and ").append(com.boss.sys.utils.SysUtils.inSql("agency_id", agencyIds));
        }
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(ThirteenAgencyVerify.class),objects);
    }

    @Override
    public void batchUpdateAgencyVerifypayStatus(List<ThirteenAgencyVerify> agencyVerifys) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" set ");
        sql.append(" is_pay_data_created = ? ");
        sql.append(" where id = ? ");
        List<Object[]> params=new ArrayList<>();
        for(ThirteenAgencyVerify agencyVerify:agencyVerifys){
            params.add( new Object[]{
                    0,agencyVerify.getId()});
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public List<ThirteenAgencyVerify> getAgencyVerifyBySalaryBank(String bank,String mofDivCode) {
        StringBuilder sql = new StringBuilder();
        List<Object> params=new ArrayList<>();
        sql.append("SELECT");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_year year,");
        sql.append(" is_submit_in_agency, is_salary_submit,");
        sql.append(" is_end, is_deleted,  audit_explain ");
        sql.append(" FROM ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append(" where is_end = 0 and is_pay_data_created=1 and mof_div_code=? AND is_deleted=? ");
        params.add(mofDivCode);
        params.add(SystemDictConstant.NOT_DELETED);
        sql.append(" and agency_id in (select DISTINCT agency_id from  ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" where sala_card_bank=? and  mof_div_code=?)");
        params.add(bank);
        params.add(mofDivCode);
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(ThirteenAgencyVerify.class),params.toArray());
    }

    @Override
    public void batchUpdateAgencyVerifyBankStatus(List<ThirteenAgencyVerify> agencyVerifys,String status) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" set ");
        sql.append(" update_time=?,is_bank_data_created = ? ");
        sql.append(" where agency_id = ? and is_end=0 and is_deleted=? ");
        List<Object[]> params=new ArrayList<>();
        String updateTime=String.valueOf(DateUtils.getCurrentTimeStamp());
        for(ThirteenAgencyVerify agencyVerify:agencyVerifys){
            params.add( new Object[]{
                    updateTime , status,agencyVerify.getAgencyId(),SystemDictConstant.NOT_DELETED});
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < sme.length; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void revokeGenerate(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" set ");
        sql.append(" is_bank_data_created = '0' ");
        sql.append(" where is_bank_data_created = '1' and is_end = 0 and mof_div_code = ?");
        this.getJdbcTemplate().update(sql.toString(),province);
    }

    @Override
    public void updateAgencyVerifyEndStatus(String province,List<String> agencyIds, String salary_year, String status) {
        StringBuilder sql = new StringBuilder();
        List<Object> params=new ArrayList<>();
        sql.append(" update ").append(TableName.THIRTEEN_AGENCY_VERIFY).append(" set ");
        sql.append(" is_end = ? ");
        sql.append(" where  mof_div_code = ? and salary_year=? and is_deleted=? and ").append(SysUtils.inSql("agency_id",agencyIds));
        params.add(status);
        params.add(province);
        params.add(salary_year);
        params.add(SystemDictConstant.NOT_DELETED);
        this.getJdbcTemplate().update(sql.toString(),params.toArray());
    }
    @Override
    public String currentYear(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" max(salary_year)");
        sql.append(" FROM ").append(TableName.THIRTEEN_AGENCY_VERIFY);
        sql.append(" where is_end = 0  and is_deleted=? and ").append(SysUtils.inSql("agency_id",agencyIds));
        return this.getJdbcTemplate().queryForObject(sql.toString(), String.class,SystemDictConstant.NOT_DELETED);
    }

}
